<?php

class mreceipt extends CI_Model {

    //ambil record pada table t_trx_sell_in berdasarkan id
    public function getById($id) {
        $id = intval($id);

        $query = $this->db->where('receipt_id', $id)->limit(1)->get('t_trx_receipt');

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }

    public function getPdfDetails($rcp_id) {
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');

        $sql =  "SELECT itm.item_name, grp.item_group_name, imp.item_code, COUNT(imp.item_code) AS jumlah ".
                "FROM t_trx_receipt_detail d ".
                "JOIN t_mtr_item itm ON itm.iccid = d.iccid ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code  ".
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id  ".
                "JOIN t_trx_receipt r ON d.receipt_id = r.receipt_id ".
                "WHERE d.receipt_id = '$rcp_id' ".
                "GROUP BY imp.item_code, itm.item_name, grp.item_group_id, r.receipt_id ";

        $query = $dbconn->query($sql);
        return $query->result();
    }

    //ambil record pada table t_mtr_item dan t_mtr_item_group
    public function getItemDetail($id) {
        $listItem = array();

        // $this->db->select('item_name, item_code, item_group_id');
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_code']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();	
        //20120507 anggwa@gmail.com **Optimasi query dan benerin masalah item group name

        $this->db->select('itm.item_name, itm.mdn, imp.item_code, grp.item_group_id, grp.item_group_name');
        $this->db->from('t_mtr_item AS itm');
        $this->db->join('t_mtr_item_mapping AS imp', 'itm.item_code = imp.item_code');
        $this->db->join('t_mtr_item_group AS grp', 'imp.item_group_id = grp.item_group_id');
        $this->db->where('iccid', $id);
        $this->db->limit(1, 0);

        $query = $this->db->get();
        $listItem = $query->result_array();

        //$listItem[0]['item_group_name'] = $q[0]['item_group_name'];
        return $listItem;
    }

    //get all records from users table
    public function getAllReceipt() {

        $res = array();
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $sql = "SELECT * FROM t_trx_receipt
				WHERE DATE_PART('day', now() - receipt_date::timestamp) 
				BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
				AND f_get_cluster(sales_id) = f_get_cluster('$userlogin')
				ORDER BY receipt_id DESC, receipt_date desc";

        $query = $dbconn->query($sql);


        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getNikName = $this->getNikName($res->sales_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }

    //edited by Alfin Akhret
     public function getAllReceiptCurrentUser() {

        $res = array();
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $sql = "SELECT * FROM t_trx_receipt
                WHERE DATE_PART('day', now() - receipt_date::timestamp) 
                BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
                AND sales_id = '$userlogin'
                ORDER BY receipt_id DESC, receipt_date desc";

        $query = $dbconn->query($sql);


        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getNikName = $this->getNikName($res->sales_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }

//end getAll

    function getOrderInfo($id) {
        $this->db->select('sales_id');
        $this->db->where('receipt_id', $id);
        $q = $this->db->get('t_trx_receipt');
        $q = $q->result_array();

        return $q[0]['sales_id'];
    }

    function getSalesName($id) {
        $this->db->select('initcap(user_name) as user_name');
        $this->db->where('user_id', $id);
        $q = $this->db->get('t_mtr_user');
        $q = $q->result_array();
        return $q[0]['user_name'];
    }

    //lakukan insert ke table t_trx_receipt
    function saveReceipt($ter_id) {
        $this->db->trans_begin();

        $dblokal = $this->load->database("default", true);
        $receipt_date = $this->input->post("receipt_date");
        $sales_id = $this->input->post("sales_id");
        $remark = $this->input->post("remark");

        $data = array(
            "territory_id" => $ter_id,
            "receipt_date" => $receipt_date,
            "sales_id" => $sales_id,
            "remark" => $remark,
            "updated_by" => $this->session->userdata('username'),
            "created_by" => $this->session->userdata('username')
        );

        $dblokal->insert("t_trx_receipt", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('receipt', 'create', $last);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();
        }

        return $last;
    }

    //lakukan insert ke table t_trx_receipt_detail
    function saveReceiptDetail($rid, $i) {
        $this->db->trans_begin();

        $dblokal = $this->load->database("default", true);
        $iccid = $this->input->post('iccid');

        $data = array(
            "iccid" => $iccid[$i],
            "receipt_id" => $rid,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username')
        );

        $dblokal->insert("t_trx_receipt_detail", $data);
        $receipt_detail_id = $this->db->insert_id();
        $this->mglobal->saveLog('receipt-detail', 'create', '(header='.$rid.'; detail='.$receipt_detail_id.'; iccid='.$iccid[$i].')');
        //update istatus pada table t_trx_scan_in_detail dari 1 menjadi 3
        $data2 = array(
            'istatus' => 3,
            'iccid' => $iccid[$i] . "#rcp#" . $rid,
            'transaction_type' => 'Receipt',
            'transaction_id' => $rid
        );
        $this->db->update('t_trx_scan_in_detail', $data2, array('iccid' => $iccid[$i]));

        //update istatus pada table t_mtr_item dari 2 menjadi 1 lagi
        $this->db->update('t_mtr_item', array('istatus' => 1), array('iccid' => $iccid[$i]));

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();
        }
    }

    //ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');

        $sql = "SELECT f_get_territory_path(f_get_cluster('$userlogin')) as territory_name, f_get_cluster('$userlogin') as territory_id";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
        //201204xx anggwa@gmail.com **Ganti pake fungsi
        // $this->db->select('territory_name, territory_id');
        // $this->db->from('t_mtr_territory');
        // $this->db->where('user_id', $this->session->userdata('username'));
        // $this->db->limit(1, 0);
        // $query = $this->db->get();
        // return $query->result_array();
    }

    //ambil daftar sales id yang terdaftar pada territory yang sama dengan user yang sedang login
    public function getSalesID($curLogin = "") {
        // $tes = "cluster";
        // $this->db->select('ter.territory_name, ter.territory_id');
        // $this->db->from('t_mtr_territory AS ter');
        // $this->db->join("t_mtr_territory_type AS terty", "ter.territory_type_id = terty.territory_type_id AND lower(terty.territory_type_name)='$tes'");
        // $this->db->where('ter.user_id', $curLogin);
        // $q = $this->db->get();
        // $query = $q->result_array();
        // $terri = $query[0]['territory_id'];
        // $this->db->select('usr.user_id, usr.user_name');
        // $this->db->group_by('usr.user_id');
        // $this->db->from('t_mtr_user AS usr');
        // $this->db->join('t_mtr_user_group AS grp', 'usr.user_group_id = grp.user_group_id AND usr.istatus = 1 AND grp.having_stock = 1');
        // $this->db->join('t_trx_scan_in AS scan', 'usr.user_id = scan.user_id AND scan.istatus = 1');
        // $this->db->join('t_mtr_user_territory AS uster', 'usr.user_id = uster.user_id');
        // $this->db->join("t_mtr_territory AS ter", "uster.territory_id = ter.territory_id AND ter.parent_id = '$terri'");
        // $this->db->order_by("usr.user_name", "asc");
        // $query = $this->db->get();
        // return $query->result();	

        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        //$sql = "SELECT DISTINCT CONCAT_WS(' || ',usr.user_id,initcap(usr.user_name)) AS user_id, usr.user_name
        // $sql = "SELECT DISTINCT usr.user_id, initcap(usr.user_name) as user_name
        // FROM t_mtr_user usr
        // JOIN t_trx_scan_in scn ON usr.user_id = scn.user_id
        // JOIN t_trx_scan_in_detail dtl ON dtl.scan_in_id = scn.scan_in_id
        // WHERE usr.reporting_to = '$userlogin'
        // AND usr.istatus = 1
        // AND dtl.istatus = 1
        // ORDER BY user_name ASC";
        //20120507 anggwa@gmail.com **Optimasi query populate user di cluster ybs

        //edited by: Alfin Akhret
        $sql = "SELECT DISTINCT usr.user_id, usr.user_name, f_get_cluster(usr.user_id) as clusterId
		FROM t_mtr_user usr, 
		t_trx_scan_in scn, t_trx_scan_in_detail d
		WHERE scn.user_id = usr.user_id 
		and scn.scan_in_id = d.scan_in_id
		and d.istatus = 1 AND usr.istatus = 1
		and f_get_cluster(usr.user_id) = f_get_cluster('$userlogin')
		ORDER BY usr.user_name ASC";

        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }

    //ambil record-record yang berkaitan dengan proses pembuatan receipt
    function getSales($sales_id) {
        //$sales_id = $this->input->post("sales_id");
        $userlogin = $this->session->userdata('username');

        $this->db->select("scand.iccid, imp.item_code, itm.mdn, grp.item_group_name, itm.item_name");
        $this->db->from('t_trx_scan_in_detail AS scand');
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1 AND scan.user_id = '$sales_id'");
        $this->db->join('t_mtr_item AS itm', 'scand.iccid = itm.iccid AND itm.istatus in  (2,5)');
        $this->db->join('t_mtr_item_mapping AS imp', 'itm.item_code = imp.item_code');
        $this->db->join('t_mtr_item_group AS grp', 'imp.item_group_id = grp.item_group_id');
        $this->db->join('t_mtr_user AS usr', 'usr.user_id = scan.user_id');
        $this->db->where("f_get_cluster(scan.user_id) = f_get_cluster('$userlogin')");  // ?????? knapa validasi cluster sales dengan cc
        $this->db->where("scan.user_id", $sales_id);
        $this->db->where("usr.istatus",1);
        // $this->db->where('');
        $query = $this->db->get();

        return $query->result_array();
    }

    //get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_price('$iccid','$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    public function get_territory($userlogin)
    {
        $this->db->select("f_get_cluster('$userlogin') as cluster, f_get_territory_path(f_get_cluster('$userlogin')) as territory", true);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function yesterday_items($sales_id)
    {
        $userlogin = $this->session->userdata('username');

        $this->db->select("scand.iccid, imp.item_code, itm.mdn, grp.item_group_name, itm.item_name");
        $this->db->from('t_trx_scan_in_detail AS scand');
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1 AND scan.user_id = '$sales_id'");
        $this->db->join('t_mtr_item AS itm', 'scand.iccid = itm.iccid AND itm.istatus in (2,5)');
        $this->db->join('t_mtr_item_mapping AS imp', 'itm.item_code = imp.item_code');
        $this->db->join('t_mtr_item_group AS grp', 'imp.item_group_id = grp.item_group_id');
        $this->db->join('t_mtr_user AS usr', 'usr.user_id = scan.user_id');
        $this->db->where("f_get_cluster(scan.user_id) = f_get_cluster('$userlogin')");  // ?????? knapa validasi cluster sales dengan cc
        $this->db->where("scan.user_id", $sales_id);
        $this->db->where("scan.created_on", "date(scan.created_on) < date('Y-m-d') ");
        $this->db->where("usr.istatus",1);
        // $this->db->where('');
        $query = $this->db->get();

        return $query->result_array();
    }
}

//end class